--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

--CREATE TABLE se_CPT_TPFTAUX AS (SELECT * FROM &erp.CPT_TPFTAUX WHERE 1=2);




DELETE FROM se_CPT_TPFTAUX;

 INSERT INTO se_CPT_TPFTAUX
 (SELECT t.tpftiers_cod,
         a.tpfart_cod,
         0 enreg_id,
         NULL famtpfa_cod,
         'TP' || Decode(t.tpftiers_cod,'A',a.tpfart_cod,t.tpftiers_cod) famtpfv_cod,

         Nvl((SELECT taux/100 FROM uv_ctpf u WHERE u.id=t.tpftiers_cod AND t.tpftiers_cod!='A')
             ,(SELECT taux/100 FROM uv_ctpf u WHERE u.id=a.tpfart_cod)) tpf_tx
    FROM &erp.cpt_tpfart a, &erp.cpt_tpftiers t ) ;


 UPDATE se_CPT_TPFTAUX tf SET enreg_id=(select enreg_id FROM &erp.CPT_TPFTAUX f WHERE f.TPFtiers_cod=tf.TPFtiers_cod AND f.TPFart_cod=tf.TPFart_cod) ;
 UPDATE se_CPT_TPFTAUX tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;



 CALL uvse_synchroverserp('TPFart_cod@TPFtiers_cod','CPT_TPFTAUX','O') ;

 commit;



select * from (
SELECT &outils.stragg(source) source,tpftiers_cod, tpfart_cod, enreg_id, famtpfa_cod, famtpfv_cod, tpf_tx
FROM (
  SELECT 'uv' source ,g.* FROM se_CPT_TPFTAUX g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.CPT_TPFTAUX g
  )
GROUP BY  tpftiers_cod, tpfart_cod, enreg_id, famtpfa_cod, famtpfv_cod, tpf_tx
HAVING Count(1)!=2) r
ORDER BY TPFtiers_cod, TPFart_cod;